using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Entities;
using Musicman.Common;

namespace Musicman.DAL
{
	public static class UserfeedbackDAO
	{
		#region Insert
		private static DbCommand insertCommand = null;
		private static string insertSQL = "INSERT INTO Musicman.dbo.UserFeedback (PERSONID, SUBJECT, FEEDBACK, DATE) VALUES (@PERSONID, @SUBJECT, @FEEDBACK, @DATE) SET @ID = SCOPE_IDENTITY()";
		private static DbCommand GetInsertCommand(SqlDatabase db)
		{
			if (insertCommand == null)
			{
				insertCommand = db.GetSqlStringCommand(insertSQL);
				db.AddOutParameter(insertCommand, "@ID", DbType.Int64, 8);
				db.AddInParameter(insertCommand, "@PERSONID", DbType.Int64);
				db.AddInParameter(insertCommand, "@SUBJECT", DbType.String);
				db.AddInParameter(insertCommand, "@FEEDBACK", DbType.String);
                db.AddInParameter(insertCommand, "@DATE", DbType.DateTime);
			}
			return insertCommand;
		}
		private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Userfeedback entity)
		{
			db.SetParameterValue(cmd, "@PERSONID", entity.Personid);
			db.SetParameterValue(cmd, "@SUBJECT", entity.Subject);
			db.SetParameterValue(cmd, "@FEEDBACK", entity.Feedback);
            db.SetParameterValue(cmd, "@DATE", (entity.Date == null) ? DateTime.Now : entity.Date);
		}
		public static bool InsertUserfeedback(Userfeedback entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			if (res)
			{
				entity.Id = (long?)db.GetParameterValue(cmd, "@ID");
			}
			return res;
		}
		public static bool InsertUserfeedback(Userfeedback entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			if (res)
			{
				entity.Id = (long?)db.GetParameterValue(cmd, "@ID");
			}
			return res;
		}
		public static bool InsertListUserfeedback(List<Userfeedback> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetInsertCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Userfeedback entity = list[i];
					SetInsertValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
					if (res)
					{
						entity.Id = (long?)db.GetParameterValue(cmd, "@ID");
					}
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool InsertListUserfeedback(List<Userfeedback> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetInsertCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Userfeedback entity = list[i];
				SetInsertValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				if (res)
				{
					entity.Id = (long?)db.GetParameterValue(cmd, "@ID");
				}
			}
			return res;
		}
		#endregion

		#region Update
		private static DbCommand updateCommand = null;
		private static string updateSQL = "UPDATE Musicman.dbo.UserFeedback SET PERSONID = @PERSONID, SUBJECT = @SUBJECT, FEEDBACK = @FEEDBACK, DATE = @DATE WHERE ID = @ID";
		private static DbCommand GetUpdateCommand(SqlDatabase db)
		{
			if (updateCommand == null)
			{
				updateCommand = db.GetSqlStringCommand(updateSQL);
				db.AddInParameter(updateCommand, "@ID", DbType.Int64);
				db.AddInParameter(updateCommand, "@PERSONID", DbType.Int64);
				db.AddInParameter(updateCommand, "@SUBJECT", DbType.String);
				db.AddInParameter(updateCommand, "@FEEDBACK", DbType.String);
                db.AddInParameter(updateCommand, "@DATE", DbType.DateTime);
			}
			return updateCommand;
		}
		private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, Userfeedback entity)
		{
			db.SetParameterValue(cmd, "@ID", entity.Id);
			db.SetParameterValue(cmd, "@PERSONID", entity.Personid);
			db.SetParameterValue(cmd, "@SUBJECT", entity.Subject);
			db.SetParameterValue(cmd, "@FEEDBACK", entity.Feedback);
            db.SetParameterValue(cmd, "@DATE", (entity.Date == null) ? DateTime.Now : entity.Date);
		}
		public static bool UpdateUserfeedback(Userfeedback entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool UpdateUserfeedback(Userfeedback entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			return res;
		}
		public static bool UpdateListUserfeedback(List<Userfeedback> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetUpdateCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Userfeedback entity = list[i];
					SetUpdateValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool UpdateListUserfeedback(List<Userfeedback> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetUpdateCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Userfeedback entity = list[i];
				SetUpdateValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			}
			return res;
		}
		
		#endregion

		#region Save
		public static bool SaveUserfeedback(Userfeedback entity)
		{
			bool res = true;
			if (entity.Id == null )
			{
				res = InsertUserfeedback(entity);
			}
			else
			{
				res = UpdateUserfeedback(entity);
			}
			return res;
		}
		public static bool SaveUserfeedback(Userfeedback entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			if (entity.Id == null )
			{
				res = InsertUserfeedback(entity, transactionObj, db);
			}
			else
			{
				res = UpdateUserfeedback(entity, transactionObj, db);
			}
			return res;
		}
		public static bool SaveListUserfeedback(List<Userfeedback> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Userfeedback entity = list[i];
					res = res & SaveUserfeedback(entity, transactionObj, db);
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool SaveListUserfeedback(List<Userfeedback> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			for(int i=0; i<list.Count; i++)
			{
				Userfeedback entity = list[i];
				res = res & SaveUserfeedback(entity, transactionObj, db);
			}
			return res;
		}
		#endregion

		#region Delete
		private static DbCommand deleteCommand = null;
		private static string deleteSQL = "DELETE FROM Musicman.dbo.UserFeedback WHERE ID = @ID";
		private static DbCommand GetDeleteCommand(SqlDatabase db)
		{
			if (deleteCommand == null)
			{
				deleteCommand = db.GetSqlStringCommand(deleteSQL);
				db.AddInParameter(deleteCommand, "@ID", DbType.Int64);
			}
			return deleteCommand;
		}
		private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, Userfeedback entity)
		{
			db.SetParameterValue(cmd, "@ID", entity.Id);
		}

		public static bool DeleteUserfeedback(Userfeedback entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetDeleteCommand(db);
			SetDeleteValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}

        public static bool DeleteUserfeedback(long feedbackID)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetDeleteCommand(db);
            db.SetParameterValue(cmd, "@ID", feedbackID);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }

		public static bool DeleteListUserfeedback(List<Userfeedback> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetDeleteCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Userfeedback entity = list[i];
					SetDeleteValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		
		#endregion

		#region Select
        private static string selectSQL = @"SELECT {0} userFeedback.ID,userFeedback.PERSONID,userFeedback.SUBJECT,userFeedback.FEEDBACK, userFeedback.DATE, users.FirstName, users.LastName
FROM Musicman.dbo.UserFeedback userFeedback 
INNER JOIN Musicman.dbo.Users users on userFeedback.PersonID = users.PersonID
{1} {2}";


        public static List<Userfeedback> LoadUserfeedbacks(string where, string orderby)
        {
            return LoadUserfeedbacks("", where, orderby);
        }

        public static List<Userfeedback> LoadUserfeedbacks(string prefix, string where, string orderby)
		{
			List<Userfeedback> res = new List<Userfeedback>();
			string sql = String.Format(selectSQL, prefix, where, orderby);
			IDataReader reader = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				reader = db.ExecuteReader(CommandType.Text, sql);
				if (reader != null)
				{
					while (reader.Read())
					{
						Userfeedback entity = new Userfeedback();
						entity.Id = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
						entity.Personid = reader.IsDBNull(1) ? null : (long?)reader.GetInt64(1);
						entity.Subject = reader.IsDBNull(2) ? null : (string)reader.GetString(2);
						entity.Feedback = reader.IsDBNull(3) ? null : (string)reader.GetString(3);
                        entity.Date = reader.IsDBNull(4) ? null : (DateTime?)reader.GetDateTime(4);
                        entity.From = (reader.IsDBNull(5) ? "" : reader.GetString(5)) + " " + (reader.IsDBNull(6) ? "" : reader.GetString(6));
						res.Add(entity);
					}
				}
				reader.Close();
			}
			catch (Exception ex)
			{
				if (reader != null && !reader.IsClosed)
				{
					reader.Close();
				}
				res = null;
				throw ex;
			}
			return res;
		}

        public static List<DateTime> LoadFeedBackDates()
        {
            string sql = "select distinct cast(convert(varchar(10), date, 101) as datetime) as date from musicman.dbo.userFeedback order by date DESC";
            List<DateTime> res = new List<DateTime>();
			IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				reader = db.ExecuteReader(CommandType.Text, sql);
				if (reader != null)
				{
					while (reader.Read())
					{
                        DateTime date = reader.GetDateTime(0);
                        res.Add(date);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

		#endregion
	}
}
